﻿using System.Linq;
using System;
using System.Drawing;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using SqlAdmin;
using System.Text;
using System.IO;
using System.Threading;
using System.Timers;
namespace BYManageInfoSystem
{
    public class ClassBackUpDataBase
    {
        private Thread m_Thread;

        readonly string databaseName = "DGProjectDB";
        readonly string strServer = @"bycw";
        readonly string strUserName = "System1";
        readonly string strPassword = "admin";

        readonly int m_nTimerPeriod = 6000;//15分钟

        private Dictionary<string, List<string>> dicTab2ForeignDepdence = new Dictionary<string, List<string>>();
        private List<string> lstTabProcessed = new List<string>();
        private ArrayList alTabSorted = new ArrayList();


        private void InitTimer()
        {
            System.Timers.Timer RecordVideoTimer = new System.Timers.Timer(m_nTimerPeriod);   //实例化Timer类，设置间隔时间为10000毫秒；
            RecordVideoTimer.Elapsed += new ElapsedEventHandler(OnTimedEvent);   //到达时间的时候执行事件
            //   Only   raise   the   event   the   first   time   Interval   elapses.   
            RecordVideoTimer.AutoReset = false;  //设置是执行一次（false）还是一直执行(true)； 
            RecordVideoTimer.Enabled = true;   //是否执行System.Timers.Timer.Elapsed事件； 
        }

        private void OnTimedEvent(object source, ElapsedEventArgs e)
        {
            m_Thread = new Thread(this.BackUpDataBase);
            m_Thread.Start();
        }

        public void Start()
        {
            //InitTimer();
        }

        public void ReadLog()
        {
            string filename = @"\TableExportSort.txt";
            string strTemp = System.Windows.Forms.Application.StartupPath;
            FileInfo fi = new FileInfo(strTemp + filename);
            if (fi.Exists)
            {
                FileStream fs = new FileStream(strTemp + filename, FileMode.Open, FileAccess.Read);
                StreamReader sr = new StreamReader(fs, System.Text.Encoding.Default);
                string strLine = sr.ReadLine();

                while (strLine != null)
                {
                    if (strLine != "")
                        alTabSorted.Add(strLine.ToString());
                    strLine = sr.ReadLine();
                }
                sr.Close();
                fs.Close();

                fi.Delete();
            }

        }

        public void WriteLog(string sMsg)
        {
            if (sMsg != "")
            {
                string filename = @"\TableExportSort.txt";
                string strTemp = System.Windows.Forms.Application.StartupPath;
                try
                {
                    FileInfo fi = new FileInfo(strTemp + filename);
                    if (!fi.Exists)
                    {
                        using (StreamWriter sw = fi.CreateText())
                        {
                            sw.WriteLine(sMsg + "\n");
                            sw.Close();
                        }
                    }
                    else
                    {
                        using (StreamWriter sw = fi.AppendText())
                        {
                            sw.WriteLine(sMsg + "\n");
                            sw.Close();
                        }
                    }
                }
                catch
                {
                }
            }
        }
        private void BackUpDataBase()
        {
            string strFolder = @"\DataBaseBak\";
            string strToday = DateTime.Now.ToString("yyyyMMdd");
            
            string strStartupPath = System.Windows.Forms.Application.StartupPath;
            FileInfo fi = new FileInfo(strStartupPath + strFolder + strToday);
            if (fi.Exists)
                return;

            bool scriptTableData = true;

            SqlServer server = new SqlServer(strServer, strUserName, strPassword, false);
            server.Connect();
            SqlDatabase database = server.Databases[databaseName];
            if (database == null)
            {
                server.Disconnect();
                // Database doesn't exist - break out and go to error page
                //MessageBox.Show("connection error");
                return;
            }

            SqlTableCollection tables = database.Tables;
            SqlStoredProcedureCollection sprocs = database.StoredProcedures;
            StringBuilder scriptResult = new StringBuilder();
            scriptResult.EnsureCapacity(400000);
            scriptResult.Append(String.Format("/* Generated on {0} */\r\n\r\n", DateTime.Now.ToString()));
            scriptResult.Append("/* Options selected: ");

            if (scriptTableData) scriptResult.Append("table-data ");

            scriptResult.Append(" */\r\n\r\n");


            // Script flow:
            // DROP and CREATE database
            // use [database]
            // GO
            // DROP sprocs
            // DROP tables
            // CREATE tables without constraints
            // Add table data
            // Add table constraints
            // CREATE sprocs


            // Use database
            scriptResult.Append(String.Format("\r\nuse [{0}]\r\nGO\r\n\r\n", databaseName));

            ReadLog();
            int nReadTabCount = alTabSorted.Count;
            int nFactTabCount = 0;
            for (int i = 0; i < tables.Count; i++)
            {

                if (tables[i].TableType == SqlObjectType.User)
                {
                    nFactTabCount++;
                }
            }

            if (alTabSorted.Count > 0 && nReadTabCount == nFactTabCount)
            {
                foreach (object o in alTabSorted)
                {
                    string strTempTabName = (string)o;
                    for (int i = 0; i < tables.Count; i++)
                    {

                        if (tables[i].TableType == SqlObjectType.User)
                        {
                            string strTemp = tables[i].Name;
                            if (strTemp == strTempTabName)
                                scriptResult.Append(tables[i].ScriptData(0));
                        }
                    }
                }
            }
            else
            {
                // Add foreign keys
                for (int i = 0; i < tables.Count; i++)
                {
                    if (tables[i].TableType == SqlObjectType.User)
                    {
                        string strTemp3 = tables[i].Name;

                        string[] result;
                        string strTemp = tables[i].ScriptSchema(SqlScriptType.ForeignKeys | (0));
                        string[] stringSeparators = new string[] { "REFERENCES [" };
                        result = strTemp.Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries);
                        int t = 0;
                        List<string> lstTemp = new List<string>();
                        if (result.Length > 1)
                        {

                            foreach (string entry in result)
                            {
                                int nIndex = entry.IndexOf(']');
                                string strTemp2 = entry.Substring(0, nIndex);
                                t++;
                                if (t > 1)
                                    lstTemp.Add(strTemp2);
                            }

                        }
                        else
                        {
                            int v = 0;
                            v++;
                        }

                        dicTab2ForeignDepdence.Add(strTemp3, lstTemp);


                    }
                }

                // Create table data
                if (scriptTableData)
                {
                    while (lstTabProcessed.Count < dicTab2ForeignDepdence.Count)
                    {
                        foreach (string strTableName in dicTab2ForeignDepdence.Keys)
                        {
                            List<string> lstTemp = dicTab2ForeignDepdence[strTableName];
                            if (lstTemp.Count == 0 && !lstTabProcessed.Contains(strTableName))
                            {
                                for (int i = 0; i < tables.Count; i++)
                                {

                                    if (tables[i].TableType == SqlObjectType.User)
                                    {
                                        string strTemp = tables[i].Name;
                                        if (strTemp == strTableName)
                                            scriptResult.Append(tables[i].ScriptData(0));
                                    }
                                }
                                lstTabProcessed.Add(strTableName);
                                WriteLog(strTableName);

                            }
                            else if (lstTemp.Count != 0 && !lstTabProcessed.Contains(strTableName))
                            {
                                bool bExport = true;
                                foreach (string strTemp in lstTemp)
                                {
                                    if (!lstTabProcessed.Contains(strTemp))
                                    {
                                        bExport = false;
                                        break;
                                    }
                                }
                                if (bExport)
                                {
                                    for (int i = 0; i < tables.Count; i++)
                                    {

                                        if (tables[i].TableType == SqlObjectType.User)
                                        {
                                            string strTemp = tables[i].Name;
                                            if (strTemp == strTableName)
                                                scriptResult.Append(tables[i].ScriptData(0));
                                        }
                                    }
                                    lstTabProcessed.Add(strTableName);
                                    WriteLog(strTableName);
                                }

                            }

                        }
                    }


                }

            }

            server.Disconnect();

            scriptResult.Append("/*-----END SCRIPT------*/");

            string theContent = scriptResult.ToString();
            //string strEncode = EncodeString(theContent);
            //string strToday = DateTime.Now.ToString("yyyyMMdd");
            

            if (!Directory.Exists(strFolder))
            {
                Directory.CreateDirectory(strFolder);
            }

            FileStream filewriter = new FileStream(strStartupPath + strFolder + strToday, FileMode.Append, FileAccess.Write);
            StreamWriter wText = new StreamWriter(filewriter);
            wText.Write(theContent);
            wText.Flush();
            filewriter.Close();

        }
    }
}
